﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.IO;

namespace Util
{
    // !!!! HoweStandardUtil 专用代码 !!!! 不能直接拷贝到 HoweUtil 项目中
    // V 1.0.9 - 2022-10-31 14:34:26
    // 1. 新增 SQLServerLocalDB 附加数据库文件连接字符串 静态方法
    // 2. 新增 SQLServer 附加数据库SQL脚本 静态方法
    // 
    // V 1.0.8 - 2021-12-18 16:48:44
    // 修复 MySQLConnStr 方法当 database 为空值时仍然输出 database =
    //
    // V 1.0.7 - 2021-11-17 16:26:29
    // 修复 SQLServerConnStr 拼接字符串端口号传空值时 仍然存在 逗号 的 Bug
    // 
    // V 1.0.6 - 2021-10-08 00:25:01
    // 1 解决 MySQL 输入中文乱码问题 ( 可以在 连接字符串 中设置 charset = 数据库对应编码 )
    // 2 新增 MySQL_EncodingList ( MySQL 常用编码列表 )
    // 
    // V 1.0.5 - 2021-05-23 17:06:57
    // 1 新增 EntityFramework 专用的 GetDataSet 方法
    // 2 新增 GetFactory方法 给 DbTransation 与 DbConnection 使用（ 由于 4.0 没有 DbProviderFactories.GetFactory(DbConnection) 的重载 ）
    // 3 常见的异常整合到 HandleCommonException 方法中统一管理
    // 
    // V 1.0.4 - 2021-05-12 16:41:39
    // 修复bug 修复 GetList T 无法读取 byte[] 的问题, 采用 JToken.FromObject 方法进行赋值
    // 
    // V 1.0.3 - 2021-04-05 16:39:09
    // 根据 Cyber 的读取SQLDataReader方式, 改写了 GetList T 方法, 
    // 优点: 能够直接读取List T, 无需再从 DataSet 中转换成为所想要的结果
    // 缺点: 若存储过程含有多个结果集, 目前只能获取第一个结果集
    //
    // V 1.0.2 - 2020-12-30 15:04:36
    // 增加参数 TimeSpan? cmdTimeoutSeconds = null 控制超时时长, 默认空值。
    // cmdTimeoutSeconds 为 null 时 CommandTimeout 默认值为 30 秒
    // 
    // V 1.0.1 - 2020-9-29 16:03:41
    // 改写 DBHelper 由原来只支持 SQLServer, 变为目前测试可以支持 SQLServer / Oracle / SQLite / MySQL 等数据库访问
    // 1 增加主流数据库工厂属性, 具体项目应先注册需要使用到的数据库Factory
    // 2 增加主流数据库连接字符串拼接工具方法
    // 可以到 https://github.com/howesdomo/SQLManager 了解使用方式
    // 
    // V 1.0.0 - 2018-09-01 16:03:28
    // 首次创建

    /// <summary>
    /// 关系型数据库工具类
    /// </summary>
    public class DBHelper
    {
        /// <summary>
        /// DbProviderFactory ( SQL Server )
        /// </summary>
        public static DbProviderFactory SQLServerFactory { get; set; }

        /// <summary>
        /// DbProviderFactory ( Oracle )
        /// </summary>
        public static DbProviderFactory OracleFactory { get; set; }

        /// <summary>
        /// DbProviderFactory ( SQLite )
        /// </summary>
        public static DbProviderFactory SQLiteFactory { get; set; }

        /// <summary>
        /// DbProviderFactory ( MySQL )
        /// </summary>
        public static DbProviderFactory MySQLFactory { get; set; }

        /// <summary>
        /// 获取 DbProviderFactory
        /// </summary>
        /// <param name="dbProvider">数据库类型 (SQL Server, SQLite, Oracle, MySQL ...)</param>
        /// <returns>DbProviderFactory</returns>
        public static DbProviderFactory GetDbProviderFactory(DbProvider dbProvider)
        {
            switch (dbProvider)
            {
                case DbProvider.Oracle:
                    if (DBHelper.OracleFactory == null)
                    {
                        throw new Exception("DBHelper.OracleFactory is null");
                    }
                    return DBHelper.OracleFactory;
                case DbProvider.SQLite:
                    if (DBHelper.SQLiteFactory == null)
                    {
                        throw new Exception("DBHelper.SQLiteFactory is null");
                    }
                    return DBHelper.SQLiteFactory;

                case DbProvider.MySQL:
                    if (DBHelper.MySQLFactory == null)
                    {
                        throw new Exception("DBHelper.MySQLFactory is null");
                    }
                    return DBHelper.MySQLFactory;
                case DbProvider.SQLServer:
                    if (DBHelper.SQLServerFactory == null)
                    {
                        throw new Exception("DBHelper.SQLServerFactory is null");
                    }
                    return DBHelper.SQLServerFactory;
                default:
                    throw new Exception($"数据库类型不在支持列表中。（DBHelper.GetDbProviderFactory 发生错误，{dbProvider}）");

            }
        }

        /// <summary>
        /// GetDataSet
        /// </summary>
        /// <param name="factory">实现 DbProviderFactory 的对象</param>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句</param>
        /// <param name="paramsList">参数列表</param>
        /// <param name="isBeginTransaction">启用事务, 默认关闭</param>
        /// <param name="isRollbackForTest">执行后回滚, 默认不回滚</param>
        /// <param name="argCommandType">执行语句类型, 默认 CommandType.StoredProcedure</param>
        /// <param name="cmdTimeoutSeconds">超时时长, 默认不设置(null值 采用默认超时时间)</param>
        /// <returns>结果集</returns>
        public static DataSet GetDataSet
        (
            DbProviderFactory factory,
            string connectionString,
            string commandText,
            List<object> paramsList,
            bool isBeginTransaction = false,
            bool isRollbackForTest = false,
            CommandType argCommandType = CommandType.StoredProcedure,
            TimeSpan? cmdTimeoutSeconds = null
        )
        {
            DataSet r = new DataSet();
            using (DbConnection conn = factory.CreateConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

                DbCommand cmd = null;
                DbTransaction tran = null;

                try
                {
                    if (isRollbackForTest == true || isBeginTransaction == true) // 开启测试回滚 或 开启事务
                    {
                        tran = conn.BeginTransaction();
                        cmd = tran.Connection.CreateCommand();
                        cmd.Transaction = tran;
                    }
                    else // 普通执行模式
                    {
                        cmd = conn.CreateCommand();
                    }

                    if (cmdTimeoutSeconds.HasValue)
                    {
                        cmd.CommandTimeout = (int)cmdTimeoutSeconds.Value.TotalSeconds;
                    }

                    cmd.CommandType = argCommandType;
                    cmd.CommandText = commandText;

                    if (paramsList != null && paramsList.Count > 0)
                    {
                        cmd.Parameters.AddRange(paramsList.ToArray());
                    }

                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        adapter.Fill(r);
                    }

                    if (tran != null)
                    {
                        if (isRollbackForTest == false)
                        {
                            tran.Commit();
                        }
                        else // 实现测试回滚
                        {
                            tran.Rollback();
                        }

                        tran.Dispose();
                    }
                }
                catch (Exception ex)
                {
                    if (tran != null)
                    {
                        tran.Rollback();
                    }

                    HandleCommonException(ex);
                }
            }

            return r;
        }

        /// <summary>
        /// EF 专用方法
        /// </summary>
        /// <param name="factory">
        ///     <para>实现 DbProviderFactory 的对象。使用方法：</para>
        ///     <para>mDbContext.Database.GetDbConnection(); </para>
        ///     <para>System.Data.Common.DbProviderFactories.GetFactory(conn);</para>
        /// </param>
        /// <param name="conn">
        ///     <para>实现 DbConnection 的对象</para>
        ///     <para>使用方法: mDbContext.Database.GetDbConnection()</para>
        /// </param>
        /// <param name="commandText">执行语句</param>
        /// <param name="paramsList">参数列表</param>
        /// <param name="isBeginTransaction">启用事务, 默认关闭</param>
        /// <param name="isRollbackForTest">执行后回滚, 默认不回滚</param>
        /// <param name="argCommandType">执行语句类型, 默认 CommandType.StoredProcedure</param>
        /// <param name="cmdTimeoutSeconds">超时时长, 默认不设置(null值 采用默认超时时间)</param>
        /// <returns>结果集</returns>
        public static DataSet GetDataSetForEF
        (
            DbProviderFactory factory,
            DbConnection conn,
            string commandText,
            List<object> paramsList,
            bool isBeginTransaction = false,
            bool isRollbackForTest = false,
            CommandType argCommandType = CommandType.StoredProcedure,
            TimeSpan? cmdTimeoutSeconds = null
        )
        {
            // EF 专用方法
            DataSet r = new DataSet();

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            DbCommand cmd = null;
            DbTransaction tran = null;

            try
            {
                if (isRollbackForTest == true || isBeginTransaction == true) // 开启测试回滚 或 开启事务
                {
                    tran = conn.BeginTransaction();
                    cmd = tran.Connection.CreateCommand();
                    cmd.Transaction = tran;
                }
                else // 普通执行模式
                {
                    cmd = conn.CreateCommand();
                }

                if (cmdTimeoutSeconds.HasValue)
                {
                    cmd.CommandTimeout = (int)cmdTimeoutSeconds.Value.TotalSeconds;
                }

                cmd.CommandType = argCommandType;
                cmd.CommandText = commandText;

                if (paramsList != null && paramsList.Count > 0)
                {
                    cmd.Parameters.AddRange(paramsList.ToArray());
                }

                using (DbDataAdapter adapter = factory.CreateDataAdapter())
                {
                    adapter.SelectCommand = cmd;
                    adapter.Fill(r);
                }

                if (tran != null)
                {
                    if (isRollbackForTest == false)
                    {
                        tran.Commit();
                    }
                    else // 实现测试回滚
                    {
                        tran.Rollback();
                    }

                    tran.Dispose();
                }
            }
            catch (Exception ex)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }

                HandleCommonException(ex);
            }

            return r;
        }

        /// <summary>
        /// GetDataSet (事务)
        /// </summary>
        /// <param name="tran">实现 DbTransaction 的实例</param>
        /// <param name="commandText">执行语句</param>        
        /// <param name="paramsList">参数列表</param>
        /// <param name="argCommandType">执行语句类型, 默认 CommandType.StoredProcedure</param>
        /// <param name="cmdTimeoutSeconds">超时时长, 默认不设置(null值 采用默认超时时间)</param>
        /// <returns>结果集</returns>
        public static DataSet GetDataSet
        (
            DbTransaction tran,
            string commandText,
            List<object> paramsList,
            CommandType argCommandType = CommandType.StoredProcedure,
            TimeSpan? cmdTimeoutSeconds = null
        )
        {
            DataSet r = new DataSet();

            try
            {
                DbCommand cmd = tran.Connection.CreateCommand();
                cmd.Transaction = tran;

                cmd.CommandType = argCommandType;
                cmd.CommandText = commandText;

                if (paramsList != null && paramsList.Count > 0)
                {
                    cmd.Parameters.AddRange(paramsList.ToArray());
                }

                if (cmdTimeoutSeconds.HasValue)
                {
                    cmd.CommandTimeout = (int)cmdTimeoutSeconds.Value.TotalSeconds;
                }

                // 由于 4.0 没有 DbProviderFactories.GetFactory(DbConnection) 的重载
                DbProviderFactory factory = GetFactory(tran);
                using (DbDataAdapter adapter = factory.CreateDataAdapter())
                {
                    adapter.SelectCommand = cmd;
                    adapter.Fill(r);
                }
            }
            catch (Exception ex)
            {
                HandleCommonException(ex);
            }

            return r;
        }

        #region GetList -- DBDataReader 直接读取 JArray, 最后转为 List<T>, 其缺点是只能获取一个结果集

        /// <summary>
        /// GetList
        /// </summary>
        /// <typeparam name="T">返回List泛型</typeparam>
        /// <param name="factory">DbProviderFactory</param>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="commandText">执行语句</param>
        /// <param name="paramsList">参数列表</param>
        /// <param name="isBeginTransaction">启用事务, 默认关闭</param>
        /// <param name="isRollbackForTest">执行后回滚, 默认不回滚</param>
        /// <param name="argCommandType">执行语句类型, 默认 CommandType.StoredProcedure</param>
        /// <param name="cmdTimeoutSeconds">超时时长, 默认不设置(null值 采用默认超时时间)</param>        
        /// <returns>数据列表</returns>
        public static List<T> GetList<T>
        (
            DbProviderFactory factory,
            string connectionString,
            string commandText,
            List<object> paramsList,
            bool isBeginTransaction = false,
            bool isRollbackForTest = false,
            CommandType argCommandType = CommandType.StoredProcedure,
            TimeSpan? cmdTimeoutSeconds = null
        )
        {
            List<T> r = null;
            using (DbConnection conn = factory.CreateConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

                DbCommand cmd = null;
                DbTransaction tran = null;

                try
                {
                    if (isRollbackForTest == true || isBeginTransaction == true) // 开启测试回滚 或 开启事务
                    {
                        tran = conn.BeginTransaction();
                        cmd = tran.Connection.CreateCommand();
                        cmd.Transaction = tran;
                    }
                    else // 普通执行模式
                    {
                        cmd = conn.CreateCommand();
                    }

                    if (cmdTimeoutSeconds.HasValue)
                    {
                        cmd.CommandTimeout = (int)cmdTimeoutSeconds.Value.TotalSeconds;
                    }

                    cmd.CommandType = argCommandType;
                    cmd.CommandText = commandText;

                    if (paramsList != null && paramsList.Count > 0)
                    {
                        cmd.Parameters.AddRange(paramsList.ToArray());
                    }

                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        r = GetList<T>(reader);
                    }

                    if (tran != null)
                    {
                        if (isRollbackForTest == false)
                        {
                            tran.Commit();
                        }
                        else // 实现测试回滚
                        {
                            tran.Rollback();
                        }

                        tran.Dispose();
                    }
                }
                catch (Exception ex)
                {
                    if (tran != null)
                    {
                        tran.Rollback();
                    }

                    HandleCommonException(ex);
                }
            }

            return r;
        }

        /// <summary>
        /// GetListForEF
        /// </summary>
        /// <typeparam name="T">返回List泛型</typeparam>
        /// <param name="conn">
        ///     <para>实现 DbConnection 的对象</para>
        ///     <para>使用方法: mDbContext.Database.GetDbConnection()</para>
        /// </param>
        /// <param name="commandText">执行语句</param>
        /// <param name="paramsList">参数列表</param>
        /// <param name="isBeginTransaction">启用事务, 默认关闭</param>
        /// <param name="isRollbackForTest">执行后回滚, 默认不回滚</param>
        /// <param name="argCommandType">执行语句类型, 默认 CommandType.StoredProcedure</param>
        /// <param name="cmdTimeoutSeconds">超时时长, 默认不设置(null值 采用默认超时时间)</param>        
        /// <returns>数据列表</returns>
        public static List<T> GetListForEF<T>
        (
            DbConnection conn,
            string commandText,
            List<object> paramsList,
            bool isBeginTransaction = false,
            bool isRollbackForTest = false,
            CommandType argCommandType = CommandType.StoredProcedure,
            TimeSpan? cmdTimeoutSeconds = null
        )
        {
            List<T> r = null;

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            DbCommand cmd = null;
            DbTransaction tran = null;

            try
            {
                if (isRollbackForTest == true || isBeginTransaction == true) // 开启测试回滚 或 开启事务
                {
                    tran = conn.BeginTransaction();
                    cmd = tran.Connection.CreateCommand();
                    cmd.Transaction = tran;
                }
                else // 普通执行模式
                {
                    cmd = conn.CreateCommand();
                }

                if (cmdTimeoutSeconds.HasValue)
                {
                    cmd.CommandTimeout = (int)cmdTimeoutSeconds.Value.TotalSeconds;
                }

                cmd.CommandType = argCommandType;
                cmd.CommandText = commandText;

                if (paramsList != null && paramsList.Count > 0)
                {
                    cmd.Parameters.AddRange(paramsList.ToArray());
                }

                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    r = GetList<T>(reader);
                }

                if (tran != null)
                {
                    if (isRollbackForTest == false)
                    {
                        tran.Commit();
                    }
                    else // 实现测试回滚
                    {
                        tran.Rollback();
                    }

                    tran.Dispose();
                }
            }
            catch (Exception ex)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }

                HandleCommonException(ex);
            }

            return r;
        }

        /// <summary>
        /// GetList
        /// </summary>
        /// <typeparam name="T">返回List泛型</typeparam>        
        /// <param name="tran">事务</param>
        /// <param name="commandText">执行语句</param>
        /// <param name="paramsList">参数列表</param>
        /// <param name="argCommandType">执行语句类型, 默认 CommandType.StoredProcedure</param>
        /// <param name="cmdTimeoutSeconds">超时时长, 默认不设置(null值 采用默认超时时间)</param>        
        /// <returns>数据列表</returns>
        public static List<T> GetList<T>
        (
            DbTransaction tran,
            string commandText,
            List<object> paramsList,
            CommandType argCommandType = CommandType.StoredProcedure,
            TimeSpan? cmdTimeoutSeconds = null
        )
        {
            try
            {
                DbCommand cmd = tran.Connection.CreateCommand();
                cmd.Transaction = tran;

                cmd.CommandType = argCommandType;
                cmd.CommandText = commandText;

                if (paramsList != null && paramsList.Count > 0)
                {
                    cmd.Parameters.AddRange(paramsList.ToArray());
                }

                if (cmdTimeoutSeconds.HasValue)
                {
                    cmd.CommandTimeout = (int)cmdTimeoutSeconds.Value.TotalSeconds;
                }

                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    return GetList<T>(reader);
                }
            }
            catch (Exception ex)
            {
                HandleCommonException(ex);
                return null;
            }
        }

        /// <summary>
        /// GetList
        /// </summary>
        /// <typeparam name="T">返回List泛型</typeparam>        
        /// <param name="dataReader">DbDataReader</param>
        /// <returns>数据列表</returns>
        public static List<T> GetList<T>(DbDataReader dataReader)
        {
            // 列名
            var cols = new List<string>();
            for (int index = 0; index < dataReader.FieldCount; index++)
            {
                cols.Add(dataReader.GetName(index));
            }

            // 赋值
            var jArray = new Newtonsoft.Json.Linq.JArray();
            while (dataReader.Read() == true)
            {
                var toAdd_JObject = new Newtonsoft.Json.Linq.JObject();

                foreach (string col in cols)
                {
                    if (dataReader[col] != DBNull.Value)
                    {
                        toAdd_JObject.Add(col, Newtonsoft.Json.Linq.JToken.FromObject(dataReader[col]));
                    }
                    else
                    {
                        toAdd_JObject.Add(col, null);
                    }
                }

                jArray.Add(toAdd_JObject);
            }

            return jArray.ToObject<List<T>>();
        }

        #endregion

        #region 根据 DbConnection / DbTransaction 获取 DbProviderFactory

        /// <summary>
        /// 通过 DbConnection 获取 DbProviderFactory
        /// </summary>
        /// <param name="conn">DbConnection</param>
        /// <returns>DbProviderFactory</returns>
        private static DbProviderFactory GetFactory(DbConnection conn)
        {
            // 由于 Standard 2.0 在 Nuget 安装了 System.Data.Common 后没有 DbProviderFactories.GetFactory(DbConnection) 的重载
            // System.Data.Common.DbProviderFactories.GetFactory(DbConnection) -- .net core 3.1
            DbProviderFactory factory = null;
            if (conn is System.Data.SqlClient.SqlConnection)
            {
                factory = GetDbProviderFactory(DbProvider.SQLServer);
            }
            else
            {
                var typeName = conn.GetType().Name;
                if (typeName.Contains("Lite"))
                {
                    factory = GetDbProviderFactory(DbProvider.SQLite);
                }
                else if (typeName.Contains("Oracle"))
                {
                    factory = GetDbProviderFactory(DbProvider.Oracle);
                }
                else if (typeName.Contains("MySQL"))
                {
                    factory = GetDbProviderFactory(DbProvider.MySQL);
                }
            }

            return factory;
        }

        /// <summary>
        /// 通过 DbTransaction 获取 DbProviderFactory
        /// </summary>
        /// <param name="tran">DbTransaction</param>
        /// <returns>DbProviderFactory</returns>
        private static DbProviderFactory GetFactory(DbTransaction tran)
        {
            // 由于 Standard 2.0 在 Nuget 安装了 System.Data.Common 后没有 DbProviderFactories.GetFactory(DbConnection) 的重载
            // System.Data.Common.DbProviderFactories.GetFactory(DbConnection) -- .net core 3.1
            DbProviderFactory factory = null;
            if (tran.Connection is System.Data.SqlClient.SqlConnection)
            {
                factory = GetDbProviderFactory(DbProvider.SQLServer);
            }
            else
            {
                var typeName = tran.Connection.GetType().Name;
                if (typeName.Contains("Lite"))
                {
                    factory = GetDbProviderFactory(DbProvider.SQLite);
                }
                else if (typeName.Contains("Oracle"))
                {
                    factory = GetDbProviderFactory(DbProvider.Oracle);
                }
                else if (typeName.Contains("MySQL"))
                {
                    factory = GetDbProviderFactory(DbProvider.MySQL);
                }
            }

            return factory;
        }

        #endregion

        #region 常见异常处理

        /// <summary>
        /// 常见异常处理
        /// </summary>
        /// <param name="ex">异常</param>
        public static void HandleCommonException(Exception ex)
        {
            if (ex.Message.StartsWith("ORA-00911"))
            {
                throw new Exception("Oracle数据库:SQL语句不能用 ; 结尾", ex);
            }
            else
            {
                throw ex;
            }
        }

        #endregion

        #region SQL Server 连接字符串

        /// <summary>
        /// SQL Server 连接字符串
        /// </summary>
        /// <param name="serverAddress">数据库服务器地址, 可以填写 IP 或 网址</param>
        /// <param name="database">数据库</param>
        /// <param name="isIntergratedSecurity">启用 Windows 身份验证, 默认 false</param>
        /// <param name="serverInstance">数据库实例名称, 默认空值, 若是 SQLExpress 版本请填写 SQLExpress</param>
        /// <param name="serverPort">数据库服务端口, 默认 1433</param>
        /// <param name="userId">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>SQL Server 连接字符串</returns>
        public static string SQLServerConnStr
        (
            string serverAddress,
            string database,
            bool isIntergratedSecurity = false,
            string serverInstance = "",
            string serverPort = "1433",
            string userId = "",
            string password = ""
        )
        {
            // Server = myServerName\myInstanceName,myPortNumber; Database = myDataBase; Integrated Security=true;;
            // Server = myServerName\myInstanceName,myPortNumber; Database = myDataBase; User Id = myUsername; Password = myPassword;

            StringBuilder sb = new StringBuilder();

            sb.Append("Server = ").Append(serverAddress);

            if (serverInstance.IsNullOrWhiteSpace() == false)
            {
                sb.Append("\\").Append(serverInstance);
            }

            if (serverPort.IsNullOrWhiteSpace() == false)
            {
                sb.Append(",").Append(serverPort);
            }

            sb.Append(";");

            sb.Append(" Database = ").Append(database).Append(";");

            if (isIntergratedSecurity)
            {
                sb.Append(" Integrated Security = true;");
            }
            else
            {
                sb.Append(" User Id = ").Append(userId).Append(";");
                sb.Append(" Password = ").Append(password).Append(";");
            }

            return sb.ToString();
        }

        /// <summary>
        /// localdb 连接字符串
        /// <para>(localdb)\localdbInstanceName; Integrated Security = true;</para>
        /// <para>查看本机 localdb 实例名称, 可以在 CMD 中运行 sqllocaldb.exe i</para>
        /// <para>实际例子:</para>
        /// <para>Server = (localdb)\ProjectsV13; Integrated Security = true;</para>
        /// </summary>
        /// <param name="database">数据库</param>
        /// <param name="serverInstance">实例名, 默认值 MSSQLLocalDB</param>
        /// <returns>localdb 连接字符串</returns>
        public static string SQLServer_LocalDBConnStr
        (
            string database = "",
            string serverInstance = ""
        )
        {
            if (string.IsNullOrWhiteSpace(serverInstance) == true)
            {
                serverInstance = "MSSQLLocalDB";
            }

            string r = $@"Server = (localdb)\{serverInstance}; Integrated Security=true;";

            if (string.IsNullOrWhiteSpace(database) == false)
            {
                r += $" Database = {database}";
            }

            return r;
        }

        /// <summary>
        /// localdb 附加数据库文件(.mdf)
        /// <para>(localdb)\localdbInstanceName; Integrated Security = true; AttachDbFileName = {fileName}</para>
        /// <para>查看本机 localdb 实例名称, 可以在 CMD 中运行 sqllocaldb.exe i</para>
        /// <para>实际例子:</para>
        /// <para>Server = (localdb)\MSSQLLocalDB; Integrated Security = true; AttachDbFileName = C:\data.mdf</para>
        /// </summary>
        /// <param name="mdfFileName">数据库文件</param>
        /// <param name="serverInstance">实例名, 默认值 MSSQLLocalDB</param>
        /// <returns></returns>
        public static string SQLServer_LocalDB_Attach_mdf_File
        (
            string mdfFileName,
            string serverInstance = ""
        )
        {
            if (string.IsNullOrWhiteSpace(serverInstance) == true)
            {
                serverInstance = "MSSQLLocalDB";
            }

            return $@"Server = (localdb)\{serverInstance}; Integrated Security = true; AttachDbFileName = {mdfFileName}";
        }

        #endregion

        #region SQLite 连接字符串

        /// <summary>
        /// 获取SQLite连接字符串
        /// </summary>
        /// <param name="dbFilePath">数据库文件名称(或路径)</param>
        /// <param name="version">数据库文件版本, 默认3。可选【2】【3】</param>
        /// <param name="isUseUTF16Encoding">使用 UTF16 编码</param>
        /// <param name="password">密码，默认空字符串</param>
        /// <param name="isReadOnly">只读连接，默认false</param>
        /// <param name="maxPoolSize">设置连接池大小，默认null</param>
        /// <param name="isFailIfMissing">默认false，默认情况下，如果数据库文件不存在，会自动创建一个新的，若设置为True，将不会创建，而是抛出异常信息</param>
        /// <returns></returns>
        public static string SQLiteConnStr
        (
            string dbFilePath,
            string version = "3",
            bool isUseUTF16Encoding = false,
            string password = "",
            bool isReadOnly = false,
            int? maxPoolSize = null,
            bool isFailIfMissing = false
        )
        {
            #region 参考资料

            // C#在不同情况下连接SQLite字符串格式
            // 1、Basic（基本的）
            // Data Source=filename;Version=3;

            // 2、Using UTF16（使用UTF16编码）
            // Data Source=filename;Version=3;UseUTF16Encoding=True;

            // 3、With password（带密码的）
            // Data Source=filename;Version=3;Password=myPassword;

            // 4、Using the pre 3.3x database format（使用3.3x前数据库格式）
            // Data Source=filename;Version=3;Legacy Format=True;

            // 5、Read only connection（只读连接）
            // Data Source=filename;Version=3;Read Only=True;

            // 6、With connection pooling（设置连接池）
            // Data Source=filename;Version=3;Pooling=False;Max Pool Size=100;

            // 7、Using DateTime.Ticks as datetime format（）
            // Data Source=filename;Version=3;DateTimeFormat=Ticks;

            // 8、Store GUID as text（把Guid作为文本存储，默认是Binary）
            // Data Source=filename;Version=3;BinaryGUID=False;

            // 如果把Guid作为文本存储需要更多的存储空间

            // 9、Specify cache size（指定Cache大小）
            // Data Source=filename;Version=3;Cache Size=2000;

            // Cache Size 单位是字节

            // 10、Specify page size（指定页大小）
            // Data Source=filename;Version=3;Page Size=1024;

            // Page Size 单位是字节

            // 11、Disable enlistment in distributed transactions
            // Data Source=filename;Version=3;Enlist=N;

            // 12、Disable create database behaviour（禁用创建数据库行为）
            // Data Source=filename;Version=3;FailIfMissing=True;

            // 默认情况下，如果数据库文件不存在，会自动创建一个新的，使用这个参数，将不会创建，而是抛出异常信息

            // 13、Limit the size of database（限制数据库大小）
            // Data Source=filename;Version=3;Max Page Count=5000;

            // The Max Page Count is measured in pages. This parameter limits the maximum number of pages of the database.

            // 14、Disable the Journal File （禁用日志回滚）
            // Data Source=filename;Version=3;Journal Mode=Off;

            // This one disables the rollback journal entirely.

            // 15、Persist the Journal File（持久）
            // Data Source=filename;Version=3;Journal Mode=Persist;

            #endregion

            StringBuilder sb = new StringBuilder();

            // ===== 基本必填 =====
            sb.Append("Data Source = ").Append(dbFilePath).Append(";").Append(" Version = ").Append(version).Append(";");

            // ===== 选填 =====
            // 使用UTF16编码
            if (isUseUTF16Encoding == true)
            {
                sb.Append(" UseUTF16Encoding = True;");
            }

            // 密码
            if (password.IsNullOrWhiteSpace() == false)
            {
                sb.Append(" Password = ").Append(password).Append(";");
            }

            // 只读连接
            if (isReadOnly == true)
            {
                sb.Append(" Read Only = True;");
            }

            // 设置连接池
            if (maxPoolSize.HasValue == true && maxPoolSize.Value > 0)
            {
                sb.Append(" Pooling = False; Max Pool Size = ").Append(maxPoolSize.Value).Append(";");
            }

            // 禁用创建数据库行为
            // 默认情况下，如果数据库文件不存在，会自动创建一个新的，若设置为True，将不会创建，而是抛出异常信息
            if (isFailIfMissing == true)
            {
                sb.Append(" FailIfMissing = True;");
            }

            return sb.ToString();
        }

        #endregion

        #region Oracle 连接字符串

        /// <summary>
        /// Oracle 连接字符串
        /// </summary>
        /// <param name="serverAddress">数据库服务器地址</param>
        /// <param name="isIntergratedSecurity">启用 Windows 身份验证, 默认 false</param>
        /// <param name="serverInstance">数据库实例名 ( ORACLE_SID )</param>
        /// <param name="serverPort">数据库服务端口, 默认 1521</param>
        /// <param name="userId">用户名, 常用管理员名称 sys, system</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public static string OracleConnStr
        (
            string serverAddress,
            bool isIntergratedSecurity = false,
            string serverInstance = "orcl",
            string serverPort = "1521",
            string userId = "",
            string password = ""
        )
        {
            // Data Source = MyOracleDB; Integrated Security=yes ;
            // Data Source = 134.127.17.1:1521/ORA; USER ID = Barcode; PASSWORD = enpot;

            StringBuilder sb = new StringBuilder();

            sb.Append("Data Source = ").Append(serverAddress);

            sb.Append(":").Append(serverPort);

            if (serverInstance.IsNullOrWhiteSpace() == false)
            {
                sb.Append("/").Append(serverInstance);
            }

            sb.Append(";");

            if (isIntergratedSecurity)
            {
                // 但目前在 .18.215 的 Oracle 服务器中测试会报错, 提示 'Integrated Security' 是无效的连接字符串属性
                // 测试环境 : win7 + oracle 11.2 
                // oracle 连接库 : Oracle.ManagedDataAccess.dll
                sb.Append(" Integrated Security = yes;");
            }
            else
            {
                sb.Append(" User Id = ").Append(userId).Append(";");
                sb.Append(" Password = ").Append(password).Append(";");
            }

            return sb.ToString();
        }

        #endregion

        /// <summary>
        /// MySQL 常用编码列表
        /// </summary>
        public static readonly List<string> MySQL_EncodingList = new List<string> { "utf8", "gbk", "gb2312", "latin1" };

        #region MySQL 连接字符串

        /// <summary>
        /// MySQL 连接字符串 
        /// </summary>
        /// <param name="serverAddress">数据库服务器地址</param>
        /// <param name="userId">用户名, 默认管理员名称 root</param>
        /// <param name="password">密码</param>
        /// <param name="serverPort">数据库服务端口, 默认 3306</param>
        /// <param name="encoding">
        ///     <para>编码(大小写敏感), 默认utf8</para>
        ///     <para>用 MySQL_EncodingList.Contain("inputValue") == true 来确认编码正确值 </para>
        /// </param>
        /// <param name="database">数据库, 大小写敏感, 可空</param>
        /// <returns>MySQL 连接字符串 </returns>
        public static string MySQLConnStr
        (
            string serverAddress,
            string userId,
            string password,
            string database = "",
            string serverPort = "3306",
            string encoding = "utf8"
        )
        {
			// Server = myServerAddress; Port = 1234; Database = myDataBase; Uid = myUsername; Pwd = myPassword; CharSet = encoding

            StringBuilder sb = new StringBuilder();

            sb.Append("Server = ").Append(serverAddress).Append(";");
            sb.Append(" Port = ").Append(serverPort).Append(";");
            sb.Append(" Uid = ").Append(userId).Append(";");
            sb.Append(" Pwd = ").Append(password).Append(";");
            sb.Append(" CharSet = ").Append(encoding).Append(";");
            if (database.IsNullOrWhiteSpace() == false)
            {
                sb.Append(" Database = ").Append(database).Append(";");
            }

            return sb.ToString();
        }

        #endregion


        #region SQL Server -- 附加数据库数据文件

        /// <summary>
        /// SQL Server -- 附加数据库数据文件
        /// </summary>
        /// <param name="database">数据库名称</param>
        /// <param name="mdfFileName">数据库数据文件(.mdf)</param>
        /// <param name="ldfFileName">选填, 不填时自动去掉扩展名.mdf, 然后补上_log.ldf</param>
        /// <returns></returns>
        public static string SQLServer_Attach_mdf_SQLScript(string database, string mdfFileName, string ldfFileName = "")
        {
            if (string.IsNullOrWhiteSpace(ldfFileName) == true)
            {
                var fi = new System.IO.FileInfo(mdfFileName);
                ldfFileName = Path.Combine(fi.DirectoryName, $"{fi.NameWithoutExtension()}_log.ldf");
            }

            string r =
$@"USE [master]
CREATE DATABASE [{database}] ON 
( FILENAME = N'{mdfFileName}' ),
( FILENAME = N'{ldfFileName}' )
 FOR ATTACH";

            return r;
        }

        #endregion
    }

    /// <summary>
    /// 数据库提供程序
    /// </summary>
    public enum DbProvider
    {
        /// <summary>
        /// SQLServer
        /// </summary>
        SQLServer,

        /// <summary>
        /// SQLite
        /// </summary>
        SQLite,

        /// <summary>
        /// Oracle
        /// </summary>
        Oracle,

        /// <summary>
        /// MySQL
        /// </summary>
        MySQL
    }
}
